insert overwrite table jms_dm.dm_business_reach_summary_month_dt partition(dt,dtype)
select
  taking_site_code,
  sign_site_code,
  subscribe_source_code,
  customer_code,
  customer_name,
  subscribe_source_name,
  taking_provider_id,
  taking_provider_desc,
  taking_city_id,
  taking_city_desc,
  taking_agent_code,
  taking_agent_name,
  taking_site_name,
  sign_provider_id,
  sign_provider_desc,
  sign_city_id,
  sign_city_desc,
  sign_agent_code,
  sign_agent_name,
  sign_site_name,
  sum(buss_all_cnt) buss_all_cnt,
  sum(buss_reach_cnt) buss_reach_cnt,
  sum(pres_reach_cnt) pres_reach_cnt,
  sum(effect_cnt) effect_cnt,
  sum(taking_diff_cnt) taking_diff_cnt,
  sum(taking_one_diff_cnt) taking_one_diff_cnt,
  sum(taking_two_diff_cnt) taking_two_diff_cnt,
  sum(taking_three_diff_cnt) taking_three_diff_cnt,
  sum(taking_four_diff_cnt) taking_four_diff_cnt,
  sum(taking_five_diff_cnt) taking_five_diff_cnt,
  sum(taking_six_diff_cnt) taking_six_diff_cnt,
  sum(taking_seven_diff_cnt) taking_seven_diff_cnt,
  sum(pres_total) pres_total,
  sum(pres_reach_sum) pres_reach_sum,
  flag,
  statistic_type,
  date(trunc(dt, 'MM')) statistic_date,
  detail_type,
  substr(trunc(dt, 'MM'), 1, 7) dt,
  dtype
from
  jms_dm.dm_business_reach_summary_dt
where
  dt >= trunc(add_months('{{ execution_date | cst_ds }}', -1), 'MM')
  and dt <= '{{ execution_date | cst_ds }}'
group by
  taking_site_code,
  sign_site_code,
  subscribe_source_code,
  customer_code,
  customer_name,
  subscribe_source_name,
  taking_provider_id,
  taking_provider_desc,
  taking_city_id,
  taking_city_desc,
  taking_agent_code,
  taking_agent_name,
  taking_site_name,
  sign_provider_id,
  sign_provider_desc,
  sign_city_id,
  sign_city_desc,
  sign_agent_code,
  sign_agent_name,
  sign_site_name,
  flag,
  statistic_type,
  detail_type,
  trunc(dt, 'MM'),
  dtype
distribute by dt, dtype, PMOD(HASH(RAND()), 3);
